use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'sp_GetCandidatesForVacancies')
  drop procedure sp_GetCandidatesForVacancies
go

create PROCEDURE sp_GetCandidatesForVacancies(@VacancyID int) 
AS
BEGIN
  if object_id('TempDB..#SelectedMandatory') is not null 
    drop table #SelectedMandatory
    
  create table #SelectedMandatory(CandidateID int, Value int)
 
  --insert record by mandatory conditions
  insert into #SelectedMandatory(CandidateID,Value)
  select c.Id, 0  
  from Candidates c
  join Vacancies v on v.Id = @VacancyID
  join Employers e on e.Id = v.EmployerID
  where (((v.Car_CompareImportantID = 1) and (v.CarID = c.CarID)) or
        (isnull(v.Car_CompareImportantID,2) = 2)) 
        and
        (((v.Age_CompareImportantID = 1) and 
          (DATEDIFF(YEAR,isnull(c.DateOfBirthDay,getdate()),GETDATE()) between 
                isnull((select a.AgeName
						from Ages a
						where a.Id = v.AgeMin),1) and
                isnull((select a.AgeName
						from Ages a
						where a.Id = v.AgeMax),100))) or
         (isnull(v.Age_CompareImportantID,2) = 2))
        and
        (((v.Sex_CompareImportantID = 1) and ((v.SexID = 3) or (v.SexID = c.SexID))) or
         (isnull(v.Sex_CompareImportantID,2) = 2))
        and
        (((v.ExpirienceYear_CompareImportantID = 1) and (v.ExpirienceYear <= (select SUM(ce.Years)	
																			  from Candidat_Expiriences ce
																			  where ce.CandidatID = c.Id and
																				    ce.PositionID = v.PositionID))) or
		 (isnull(v.ExpirienceYear_CompareImportantID,2) = 2))
		and
		((((v.Position_CompareImportantID = 1) or (c.Position_CompareImportantID = 1)) and
		   (v.PositionID = c.PositionID)) or
		 ((isnull(v.Position_CompareImportantID,2) = 2) and (isnull(c.Position_CompareImportantID,2) = 2)))
		and
		(((c.SalaryRange_CompareImportantID = 1) and (c.SalaryRangeID <= v.SalaryRangeID)) or
		 (isnull(c.SalaryRange_CompareImportantID,2) = 2))
		and
		(((c.Territory_CompareImportantID = 1) and (c.RangeTerritoryID = v.TerritoryId)) or
		 (isnull(c.Territory_CompareImportantID,2) = 2))
		and
		e.Id not in (select cee.EmployerID
					 from Candidat_ExceptEmployers cee
					 where cee.CandidatID = c.Id)
		and 
		(
		exists(select 1
			   from Vacancy_CommodityGroups vcg
			   where vcg.VacancyId = v.Id and
			         vcg.CommodityGroupId not in (select cecg.CommodityGroupId
												  from Candidat_ExceptCommodityGroups cecg
												  where cecg.CandidatID = c.Id))
		or
		exists(select 1
			   from Vacancy_CommodityGroups vcg
			   where vcg.VacancyId = v.Id
			   having COUNT(1) = 0)
	    )
		 
  -- update record value by passed conditions
  update sm set
    sm.Value = case
			     when v.CarID = c.CarID then 1
				 else 0
			   end +
			   case 
			     when DATEDIFF(YEAR,isnull(c.DateOfBirthDay,getdate()),GETDATE()) between v.AgeMin and v.AgeMax
			       then 1
			     else 0
			   end +
			   case 
			     when (v.SexID = 3) or (v.SexID = c.SexID) then 1
			     else 0
			   end +
			   case 
			     when v.ExpirienceYear <= (select SUM(ce.Years)	
										   from Candidat_Expiriences ce
										   where ce.CandidatID = c.Id and
										         ce.PositionID = v.PositionID) then 1
                 else 0
               end +
               case 
                 when v.PositionID = c.PositionID then 1
                 else 0
               end +
               case 
                 when c.SalaryRangeID <= v.SalaryRangeID then 1
                 else 0
               end +
               case
                 when c.RangeTerritoryID = v.TerritoryId then 1
                 else 0
               end
  from #SelectedMandatory sm
  join Candidates c on c.Id = sm.CandidateID
  join Vacancies v on v.Id = @VacancyID
  
  
  --output
  select c.*
  from Candidates c
  join #SelectedMandatory sm on c.Id = sm.CandidateID
  order by sm.Value desc
END
GO
